<?php

namespace app\admin\controller;

use think\Controller;
use think\Db;

class Test extends Controller
{
    public function index()
    {
//        2.1快捷查询
        $row = Db::name('user')
             ->where('id',5)
             ->find();

        $row = Db::name('user')
            ->where('id','<=',5)
            ->select();

        $row = Db::name('user')
            ->where('age','<=',20)
            ->where('name','like','%许%')
            ->select();

        $row = Db::name('user')
            ->where('sex','0')
            ->whereOr('province','上海')
            ->select();

        //2.2批量查询
        $row = Db::name('user')
             ->where([
                 'id'=>['between','10,20'],
                 'sex' => ['eq',0]
             ])->select();

        $row = Db::name('user')
            ->where('sex',1)
            ->where('id',['between','1,3'],['in',[6, 66, 666]],'or')
            ->select();

        //2.3区间查询
        $row = Db::name('user')
            ->where('id',['>',2],['<',15])
            ->select();
        //多表查询
//        $row = Db::field(['u.name'=>'un','l.name'=>'ln'])
//                ->table(['hc_user'=>'u','hc_lover'=>'1'])
//                ->where('u.id = l.user_id')
//                ->select();

//        $row = Db::name('')
//
        //join
//        $row =Db::table('hc_user')
//            ->alias('u')
//            ->filed('')
//        视图

        $row = Db::name('user')
            ->field('province, group_concat(name)')
            ->group('province')
            ->select();
        var_dump($row);


        $query = Db::table('hc_user')
            ->field('id, name, sex, age')
            ->where('sex',0)
            ->buildSql();
//        dump($query);
        // 组装 完整的SQL
        $row = Db::table($query . ' u')
            ->where('u.age','>',18)
            ->order('id', 'desc')
            ->select();

        dump($row);
    }

    }
//    聚合查询


}
